package com.its.en.service;

import com.its.en.entity.ExportTable;
import com.its.en.utils.ConstantUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: master
 * @Version: 1.0
 */
@Service
public class TestService {

    private List<ExportTable> getListData() {
        // 模拟对象数据
        ExportTable exportTable = new ExportTable();
        exportTable.setFeed_product("1");
        exportTable.setItem_sku("2");
        exportTable.setBrand_name("3");
        exportTable.setItem_name("4");
        exportTable.setExternal_product_id("5");
        exportTable.setExternal_product_id_type("6");
        exportTable.setManufacturer("7");
        exportTable.setQuantity("8");
        exportTable.setStandard_price("9");
        exportTable.setMain_image_url("10");
        exportTable.setOther_image_url1("11");
        exportTable.setOther_image_url2("12");
        exportTable.setOther_image_url3("13");
        exportTable.setOther_image_url4("14");
        exportTable.setOther_image_url5("15");
        exportTable.setOther_image_url6("16");
        exportTable.setOther_image_url7("17");
        exportTable.setOther_image_url8("18");
        exportTable.setUpdate_delete("19");
        exportTable.setPart_number("20");
        exportTable.setProduct_description("21");
        exportTable.setBullet_point1("22");
        exportTable.setBullet_point2("23");
        exportTable.setBullet_point3("24");
        exportTable.setBullet_point4("25");
        exportTable.setBullet_point5("26");
        exportTable.setParent_child("27");
        exportTable.setParent_sku("28");
        exportTable.setRelationship_type("29");
        exportTable.setVariation_theme("30");
        exportTable.setGeneric_keywords("31");
        exportTable.setColor_name("32");
        exportTable.setColor_map("33");
        exportTable.setSize_name("34");
        exportTable.setSize_map("35");
        exportTable.setCurrency("36");
        exportTable.setCondition_type("37");
        exportTable.setWebsite_shipping_weight_unit_of_measure("38");

        // 模拟对象数据2
        ExportTable exportTable2 = new ExportTable();
        exportTable2.setFeed_product("1");
        exportTable2.setItem_sku("2");
        exportTable2.setBrand_name("3");
        exportTable2.setItem_name("4");
        exportTable2.setExternal_product_id("5");
        exportTable2.setExternal_product_id_type("6");
        exportTable2.setManufacturer("7");
        exportTable2.setQuantity("8");
        exportTable2.setStandard_price("9");
        exportTable2.setMain_image_url("10");
        exportTable2.setOther_image_url1("11");
        exportTable2.setOther_image_url2("12");
        exportTable2.setOther_image_url3("13");
        exportTable2.setOther_image_url4("14");
        exportTable2.setOther_image_url5("15");
        exportTable2.setOther_image_url6("16");
        exportTable2.setOther_image_url7("17");
        exportTable2.setOther_image_url8("18");
        exportTable2.setUpdate_delete("19");
        exportTable2.setPart_number("20");
        exportTable2.setProduct_description("21");
        exportTable2.setBullet_point1("22");
        exportTable2.setBullet_point2("23");
        exportTable2.setBullet_point3("24");
        exportTable2.setBullet_point4("25");
        exportTable2.setBullet_point5("26");
        exportTable2.setParent_child("27");
        exportTable2.setParent_sku("28");
        exportTable2.setRelationship_type("29");
        exportTable2.setVariation_theme("30");
        exportTable2.setGeneric_keywords("31");
        exportTable2.setColor_name("32");
        exportTable2.setColor_map("33");
        exportTable2.setSize_name("34");
        exportTable2.setSize_map("35");
        exportTable2.setCurrency("36");
        exportTable2.setCondition_type("37");
        exportTable2.setWebsite_shipping_weight_unit_of_measure("38");

        // 模拟封装数据
        List<ExportTable> listDetails = new ArrayList<>();
        listDetails.add(exportTable);
        listDetails.add(exportTable2);
        return listDetails;
    }

    public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
        // 重置
        response.reset();

        //1 创建模板文件
        //1.1创建Excel
        HSSFWorkbook wb = new HSSFWorkbook();
        //1.2创建sheet
        HSSFSheet sheet1 = wb.createSheet("master-data");

        sheet1.setColumnWidth(0, 4000);
        sheet1.setColumnWidth(1, 4000);
        sheet1.setColumnWidth(2, 4000);
        sheet1.setColumnWidth(3, 8000);
        sheet1.setColumnWidth(4, 8000);
        sheet1.setColumnWidth(5, 4000);
        sheet1.setColumnWidth(6, 8000);
        sheet1.setColumnWidth(7, 8000);
        sheet1.setColumnWidth(8, 8000);
        sheet1.setColumnWidth(9, 8000);
        sheet1.setColumnWidth(10, 8000);
        sheet1.setColumnWidth(11, 8000);
        sheet1.setColumnWidth(12, 8000);
        sheet1.setColumnWidth(13, 8000);
        sheet1.setColumnWidth(14, 8000);
        sheet1.setColumnWidth(15, 8000);
        sheet1.setColumnWidth(16, 8000);
        sheet1.setColumnWidth(17, 8000);
        sheet1.setColumnWidth(18, 8000);
        sheet1.setColumnWidth(19, 8000);
        sheet1.setColumnWidth(20, 8000);
        sheet1.setColumnWidth(21, 8000);
        sheet1.setColumnWidth(22, 8000);
        sheet1.setColumnWidth(23, 8000);
        sheet1.setColumnWidth(24, 8000);
        sheet1.setColumnWidth(25, 8000);
        sheet1.setColumnWidth(26, 8000);
        sheet1.setColumnWidth(27, 8000);
        sheet1.setColumnWidth(28, 8000);
        sheet1.setColumnWidth(29, 8000);
        sheet1.setColumnWidth(30, 8000);
        sheet1.setColumnWidth(31, 8000);
        sheet1.setColumnWidth(32, 8000);
        sheet1.setColumnWidth(33, 8000);
        sheet1.setColumnWidth(34, 8000);
        sheet1.setColumnWidth(35, 8000);
        sheet1.setColumnWidth(36, 8000);
        sheet1.setColumnWidth(37, 8000);

        // 设置表头1
        String[] topHeader = new String[]{"", "", "Discovery",
                "", "", "", "", "",
                "", "", "", "", "",
                "", "", "", "", "",
                "", "", "", "", "", "",
                "", "", "", "", "", "",
                "", "", "", "", "", "", "",
                ""};

        //设置导出表头2
        String[] header = new String[]{"feed_product", "item_sku", "brand_name",
                "item_name", "external_product_id", "external_product_id_type", "manufacturer", "quantity",
                "standard_price", "main_image_url", "other_image_url1", "other_image_url2", "other_image_url3",
                "other_image_url4", "other_image_url5", "other_image_url6", "other_image_url7", "other_image_url8",
                "update_delete", "part_number", "product_description", "bullet_point1", "bullet_point2", "bullet_point3",
                "bullet_point4", "bullet_point5", "parent_child", "parent_sku", "relationship_type", "variation_theme",
                "generic_keywords", "color_name", "color_map", "size_name", "size_map", "currency", "condition_type",
                "website_shipping_weight_unit_of_measure"};

        //设置导出表头的简介
        String[] headerDetail = new String[]{"feed_product-detail", "item_sku", "brand_name",
                "item_name", "external_product_id", "external_product_id_type", "manufacturer", "quantity",
                "standard_price", "main_image_url", "other_image_url1", "other_image_url2", "other_image_url3",
                "other_image_url4", "other_image_url5", "other_image_url6", "other_image_url7", "other_image_url8",
                "update_delete", "part_number", "product_description", "bullet_point1", "bullet_point2", "bullet_point3",
                "bullet_point4", "bullet_point5", "parent_child", "parent_sku", "relationship_type", "variation_theme",
                "generic_keywords", "color_name", "color_map", "size_name", "size_map", "currency", "condition_type",
                "website_shipping_weight_unit_of_measure"};

        HSSFCell cell = null;
        // 列头的样式
        HSSFFont columnHeadFont = wb.createFont();
        columnHeadFont.setFontName("宋体");
        columnHeadFont.setFontHeightInPoints((short) 10);
        columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        //HSSFCellStyle columnHeadStyle = getCellStyle(wb, columnHeadFont, "white");

        // todo: 1行开始
        HSSFRow rowHeaderTop = sheet1.createRow(0);
        //插入第二行数据的表头
        for (int i = 0; i < topHeader.length; i++) {
            cell = rowHeaderTop.createCell(i);
            cell.setCellValue(topHeader[i]);
            if (i < 2) {
                cell.setCellStyle(getCellOrangeStyle(wb, columnHeadFont));
            } else {
                cell.setCellStyle(getCellGreenStyle(wb, columnHeadFont));
            }

        }

        // todo: 2行开始
        HSSFRow rowHeader = sheet1.createRow(1);
        //插入第二行数据的表头
        for (int i = 0; i < header.length; i++) {
            cell = rowHeader.createCell(i);
            cell.setCellValue(header[i]);
            if (i < 2) {
                cell.setCellStyle(getCellOrangeStyle(wb, columnHeadFont));
            } else {
                cell.setCellStyle(getCellGreenStyle(wb, columnHeadFont));
            }
        }

        // todo: 3行开始
        //插入第三行数据的表头简介
        HSSFRow rowDetail = sheet1.createRow(2);
        //插入第一行数据的表头
        for (int i = 0; i < headerDetail.length; i++) {
            cell = rowDetail.createCell(i);
            cell.setCellValue(headerDetail[i]);
            if (i < 2) {
                cell.setCellStyle(getCellOrangeStyle(wb, columnHeadFont));
            }else{
                cell.setCellStyle(getCellGreenStyle(wb, columnHeadFont));
            }
        }

        List<ExportTable> listDetails = this.getListData();
        if (listDetails.size() > 0) {
            //cell.setCellStyle(getCellWhiteStyle(wb, columnHeadFont));
            HSSFRow row1;
            for (int i = 0; i < listDetails.size(); i++) {
                // todo: 数据从第四行开始
                row1 = sheet1.createRow(3 + i);
                row1.createCell(0).setCellValue(listDetails.get(i).getFeed_product());
                row1.createCell(1).setCellValue(listDetails.get(i).getItem_sku());
                row1.createCell(2).setCellValue(listDetails.get(i).getBrand_name());
                row1.createCell(3).setCellValue(listDetails.get(i).getItem_name());
                row1.createCell(4).setCellValue(listDetails.get(i).getExternal_product_id());
                row1.createCell(5).setCellValue(listDetails.get(i).getExternal_product_id_type());
                row1.createCell(6).setCellValue(listDetails.get(i).getManufacturer());
                row1.createCell(7).setCellValue(listDetails.get(i).getQuantity());
                row1.createCell(8).setCellValue(listDetails.get(i).getStandard_price());
                row1.createCell(9).setCellValue(listDetails.get(i).getMain_image_url());
                row1.createCell(10).setCellValue(listDetails.get(i).getOther_image_url1());
                row1.createCell(11).setCellValue(listDetails.get(i).getOther_image_url2());
                row1.createCell(12).setCellValue(listDetails.get(i).getOther_image_url3());
                row1.createCell(13).setCellValue(listDetails.get(i).getOther_image_url4());
                row1.createCell(14).setCellValue(listDetails.get(i).getOther_image_url5());
                row1.createCell(15).setCellValue(listDetails.get(i).getOther_image_url6());
                row1.createCell(16).setCellValue(listDetails.get(i).getOther_image_url7());
                row1.createCell(17).setCellValue(listDetails.get(i).getOther_image_url8());
                row1.createCell(18).setCellValue(listDetails.get(i).getUpdate_delete());
                row1.createCell(19).setCellValue(listDetails.get(i).getPart_number());
                row1.createCell(20).setCellValue(listDetails.get(i).getProduct_description());
                row1.createCell(21).setCellValue(listDetails.get(i).getBullet_point1());
                row1.createCell(22).setCellValue(listDetails.get(i).getBullet_point2());
                row1.createCell(23).setCellValue(listDetails.get(i).getBullet_point3());
                row1.createCell(24).setCellValue(listDetails.get(i).getBullet_point4());
                row1.createCell(25).setCellValue(listDetails.get(i).getBullet_point5());
                row1.createCell(26).setCellValue(listDetails.get(i).getParent_child());
                row1.createCell(27).setCellValue(listDetails.get(i).getParent_sku());
                row1.createCell(28).setCellValue(listDetails.get(i).getRelationship_type());
                row1.createCell(29).setCellValue(listDetails.get(i).getVariation_theme());
                row1.createCell(30).setCellValue(listDetails.get(i).getGeneric_keywords());
                row1.createCell(31).setCellValue(listDetails.get(i).getColor_name());
                row1.createCell(32).setCellValue(listDetails.get(i).getColor_map());
                row1.createCell(33).setCellValue(listDetails.get(i).getSize_name());
                row1.createCell(34).setCellValue(listDetails.get(i).getSize_map());
                row1.createCell(35).setCellValue(listDetails.get(i).getCurrency());
                row1.createCell(36).setCellValue(listDetails.get(i).getCondition_type());
                row1.createCell(37).setCellValue(listDetails.get(i).getWebsite_shipping_weight_unit_of_measure());
            }
        }

        String filename = "master-data" + System.currentTimeMillis() + ".xls";
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;fileName=" + filename);
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }

    /*private HSSFCellStyle getCellWhiteStyle(HSSFWorkbook wb, HSSFFont columnHeadFont) {
        HSSFCellStyle columnHeadStyle = getCommonStyle(wb, columnHeadFont);
        columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        columnHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return columnHeadStyle;
    }*/

    private HSSFCellStyle getCellOrangeStyle(HSSFWorkbook wb, HSSFFont columnHeadFont) {
        HSSFCellStyle columnHeadStyle = getCommonStyle(wb, columnHeadFont);
        columnHeadStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
        columnHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return columnHeadStyle;
    }

    private HSSFCellStyle getCellGreenStyle(HSSFWorkbook wb, HSSFFont columnHeadFont) {
        HSSFCellStyle columnHeadStyle = getCommonStyle(wb, columnHeadFont);
        // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）
        columnHeadStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
        columnHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return columnHeadStyle;
    }

    private HSSFCellStyle getCommonStyle(HSSFWorkbook wb, HSSFFont columnHeadFont) {
        HSSFCellStyle columnHeadStyle = wb.createCellStyle();
        columnHeadStyle.setFont(columnHeadFont);
        // 左右居中
        columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 上下居中
        columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        columnHeadStyle.setLocked(true);
        columnHeadStyle.setWrapText(true);
        // 左边框的颜色
        columnHeadStyle.setLeftBorderColor(HSSFColor.GREEN.index);
        // 边框的大小
        columnHeadStyle.setBorderLeft((short) 1);
        // 右边框的颜色
        columnHeadStyle.setRightBorderColor(HSSFColor.GREEN.index);
        // 边框的大小
        columnHeadStyle.setBorderRight((short) 1);
        // 设置单元格的边框为粗体
        columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置单元格的边框颜色
        columnHeadStyle.setBottomBorderColor(HSSFColor.GREEN.index);
        return columnHeadStyle;
    }
}
